﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;

using ProfileManager.Helpers;
using ProfileManager.Properties;

namespace ProfileManager.DataAccess
{
    public class ServiceInfo
    {
        public Guid Id { get; set; }
        public Guid UserInfoId { get; set; }
        public Guid ServiceTypeId { get; set; }
        public string Name { get; set; }
        public string Url { get; set; }
        public Image IconImage { get; set; }
        public DateTime DateCreated { get; set; }
        public bool Deleted { get; set; }

        public ServiceInfo()
        {

        }

        public ServiceInfo(string name, Guid serviceTypeId)
        {
            Name = name;
            ServiceTypeId = serviceTypeId;
            UserInfoId = Globals.CurrentUser.Id;
        }

        public void Insert()
        {
            try
            {
                if (!IsExist())
                {
                    using (SqlConnection con = CommonDb.GetBaseConection())
                    {
                        Guid newId = Guid.NewGuid();

                        SqlCommand saveCmd = new SqlCommand("ServiceInfoInsert", con);
                        saveCmd.CommandType = CommandType.StoredProcedure;

                        saveCmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = newId;
                        saveCmd.Parameters.Add("@UserInfoId", SqlDbType.UniqueIdentifier).Value = UserInfoId;
                        saveCmd.Parameters.Add("@ServiceTypeId", SqlDbType.UniqueIdentifier).Value = ServiceTypeId;
                        saveCmd.Parameters.Add("@Name", SqlDbType.VarBinary).Value = Crypto.Encrypt(Name);
                        saveCmd.Parameters.Add("@Url", SqlDbType.VarBinary).Value = Crypto.Encrypt(Url);
                        saveCmd.Parameters.Add("@IconImage", SqlDbType.Image).Value = DBNull.Value;

                        con.Open();

                        bool isRowsAffected = saveCmd.ExecuteNonQuery() > 0;
                        if (isRowsAffected)
                        {
                            Id = newId;
                        }
                    }
                }
                else
                {
                    ShowMessage.Error(Resources.SelectedServiceTypeIsExist);
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
            }
        }

        public bool Update()
        {
            try
            {
                if (IsExist())
                {
                    using (SqlConnection con = CommonDb.GetBaseConection())
                    {
                        SqlCommand updCmd = new SqlCommand("ServiceInfoUpdate", con);
                        updCmd.CommandType = CommandType.StoredProcedure;

                        updCmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = Id;
                        updCmd.Parameters.Add("@UserInfoId", SqlDbType.UniqueIdentifier).Value = UserInfoId;
                        updCmd.Parameters.Add("@ServiceTypeId", SqlDbType.UniqueIdentifier).Value = ServiceTypeId;
                        updCmd.Parameters.Add("@Name", SqlDbType.VarBinary).Value = Crypto.Encrypt(Name);
                        updCmd.Parameters.Add("@Url", SqlDbType.VarBinary).Value = Crypto.Encrypt(Url);
                        updCmd.Parameters.Add("@IconImage", SqlDbType.Image).Value = DBNull.Value;

                        con.Open();
                        return updCmd.ExecuteNonQuery() > 0;
                    }
                }
                return false;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        public static bool Delete(Guid id)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetBaseConection())
                {
                    SqlCommand delCmd = new SqlCommand("ServiceInfoDelete", con);
                    delCmd.CommandType = CommandType.StoredProcedure;

                    delCmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = id;

                    con.Open();
                    return delCmd.ExecuteNonQuery() > 0;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        public static ServiceInfo Select(Guid id)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetBaseConection())
                {
                    SqlCommand getCmd = new SqlCommand("ServiceInfoSelectById", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = id;

                    con.Open();
                    List<ServiceInfo> cList = MapFrom(getCmd.ExecuteReader());

                    return cList != null ? cList.FirstOrDefault() : null;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        public static List<ServiceInfo> SelectByServiceTypeId(Guid serviceTypeId)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetBaseConection())
                {
                    SqlCommand getCmd = new SqlCommand("ServiceInfoSelectByServiceTypeId", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@ServiceTypeId", SqlDbType.UniqueIdentifier).Value = serviceTypeId;

                    con.Open();
                    List<ServiceInfo> cList = MapFrom(getCmd.ExecuteReader());

                    return cList;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        private bool IsExist()
        {
            try
            {
                using (SqlConnection con = CommonDb.GetBaseConection())
                {
                    SqlCommand getCmd = new SqlCommand("ServiceInfoIsExist", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = Id != Guid.Empty ? (object)Id : DBNull.Value;
                    getCmd.Parameters.Add("@UserInfoId", SqlDbType.UniqueIdentifier).Value = UserInfoId;
                    getCmd.Parameters.Add("@ServiceTypeId", SqlDbType.UniqueIdentifier).Value = ServiceTypeId;
                    getCmd.Parameters.Add("@Name", SqlDbType.VarBinary).Value = Crypto.Encrypt(Name);

                    con.Open();
                    return Convert.ToInt32(getCmd.ExecuteScalar()) > 0;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return true;
            }
        }

        private static List<ServiceInfo> MapFrom(SqlDataReader dr)
        {
            try
            {
                if (dr != null && dr.HasRows)
                {
                    List<ServiceInfo> acList = new List<ServiceInfo>();
                    while (dr.Read())
                    {
                        ServiceInfo wt = new ServiceInfo
                        {
                            Id = Guid.Parse(dr["Id"].ToString()),
                            Name = Crypto.Decrypt((byte[])dr["Name"]),
                            Url = Crypto.Decrypt((byte[])dr["Url"]),
                            UserInfoId = Guid.Parse(dr["UserInfoId"].ToString()),
                            ServiceTypeId = Guid.Parse(dr["ServiceTypeId"].ToString()),
                            DateCreated = Convert.ToDateTime(dr["DateCreated"]),
                            Deleted = Convert.ToBoolean(dr["Deleted"]),
                            IconImage = null
                        };

                        acList.Add(wt);
                    }
                    return acList;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }
    }
}
